-- @owner: xuwenfei1
-- @date: 2025/3/21
-- @testpoint: 存储过程异常相关语法,参数测试，部分功能合理报错

@conn PrimaryDbAmode;
--step0：前置条件;expect: 成功
drop PROCEDURE if exists proc_exception_01;
drop table if exists employees_01;
drop PROCEDURE if exists account_status1_01;
drop PROCEDURE if exists account_status2_01;
drop PROCEDURE if exists account_status3_01;
drop PROCEDURE if exists account_status4_01;
drop PROCEDURE if exists account_status5_01;
drop PROCEDURE if exists pro6_01;

--step1: 创建和调用存储过程;expect: 成功
CREATE OR REPLACE PROCEDURE proc_exception_01 (v_personid INT)
IS
v_name VARCHAR(50):='aaa';
v_email VARCHAR(50):='bbbb';
e1 EXCEPTION;
PRAGMA EXCEPTION_INIT (e1, -20001);
BEGIN
    IF v_personid=1 THEN
        RAISE e1;
    ELSE
        raise info 'INFO:%', v_name || v_email;
    END IF;
EXCEPTION
    WHEN E1 THEN
        raise info 'INFO:%', v_name ||' '||SQLCODE||' '||SQLERRM;
END;
/
call proc_exception_01(1);
call proc_exception_01(10);
create table employees_01(name varchar,empno int,salary int);
DECLARE
    v_empid employees_01.empno%TYPE;
    v_sal employees_01.salary%TYPE;
    SALARY_NOT_SET EXCEPTION;
BEGIN
    v_empid := 113;
    SELECT salary INTO v_sal FROM employees_01
        WHERE empno=v_empid;
    IF v_sal<=10000 THEN
        UPDATE employees_01 SET salary=salary+100 WHERE empno=v_empid;
        raise info 'INFO:Employee % updated',v_empid;
    ELSIF v_sal is NULL THEN
        RAISE SALARY_NOT_SET;
    ELSE
        raise info 'INFO:Employee % ignored',v_empid;
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        raise info 'INFO:Employee % not found',v_empid;
    WHEN SALARY_NOT_SET THEN
        raise info 'INFO:Salary not set: %',v_empid;
    WHEN OTHERS THEN
        raise info 'INFO:% --- %',SQLCODE,SQLERRM;
END;
/
insert into employees_01 values('lili',113,9999);
DECLARE
    v_empid employees_01.empno%TYPE;
    v_sal employees_01.salary%TYPE;
    SALARY_NOT_SET EXCEPTION;
BEGIN
    v_empid := 113;
    SELECT salary INTO v_sal FROM employees_01
        WHERE empno=v_empid;
    IF v_sal<=10000 THEN
        UPDATE employees_01 SET salary=salary+100 WHERE empno=v_empid;
        raise info 'INFO:Employee % updated',v_empid;
    ELSIF v_sal is NULL THEN
        RAISE SALARY_NOT_SET;
    ELSE
        raise info 'INFO:Employee % ignored',v_empid;
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        raise info 'INFO:Employee % not found',v_empid;
    WHEN SALARY_NOT_SET THEN
        raise info 'INFO:Salary not set: %',v_empid;
    WHEN OTHERS THEN
        raise info 'INFO:% --- %',SQLCODE,SQLERRM;
END;
/

--step2: 执行匿名块;expect: 成功
DECLARE
    v_empid employees_01.empno%TYPE;
    v_sal employees_01.salary%TYPE;
    SALARY_NOT_SET EXCEPTION;
BEGIN
    v_empid := 113;
    SELECT salary INTO v_sal FROM employees_01
        WHERE empno=v_empid;
    IF v_sal<=10000 THEN
        UPDATE employees_01 SET salary=salary+100 WHERE empno=v_empid;
        raise info 'INFO:Employee % updated',v_empid;
    ELSIF v_sal is NULL THEN
        RAISE SALARY_NOT_SET;
    ELSE
        raise info 'INFO:Employee % ignored',v_empid;
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        raise info 'INFO:Employee % not found',v_empid;
    WHEN SALARY_NOT_SET THEN
        raise info 'INFO:Salary not set: %',v_empid;
    WHEN OTHERS THEN
        raise info 'INFO:% --- %',SQLCODE,SQLERRM;
END;
/

--step3: 创建和调用存储过程;expect: 创建成功，调用存储过程报指定错误
CREATE PROCEDURE account_status1_01 (
    due_date DATE,
    today DATE
) AUTHID DEFINER
IS
BEGIN
    IF due_date < today THEN -- explicitly raise exception
        RAISE_APPLICATION_ERROR(-20000, 'Account past due.');
    END IF;
END;
/
call account_status1_01('2024-1-1','2023-1-1');
call account_status1_01('2024-1-1','2024-10-1');
CREATE PROCEDURE account_status2_01 (
    due_date DATE,
    today DATE
) AUTHID DEFINER
IS
BEGIN
    IF due_date < today THEN
        RAISE_APPLICATION_ERROR(-20999, '帐户逾期。');
    ELSIF due_date = today THEN
        RAISE_APPLICATION_ERROR(-20001, '0123456789');
    ELSE
        RAISE_APPLICATION_ERROR(-20909, '+-*/.&^$#@!~<>[]{}\|`"');
    END IF;
END;
/
call account_status2_01('2024-1-1','2024-10-1');
call account_status2_01('2024-1-1','2024-1-1');
call account_status2_01('2024-1-1','2023-1-1');

--step4: 创建存储过程;expect: message信息较长，创建存储过程成功，调用存储过程报指定错误
CREATE or replace PROCEDURE account_status3_01
IS
BEGIN
    RAISE_APPLICATION_ERROR(-20818, '帐户逾期+-*/.&^$#@!~<>[]{}\|`"0123456789。0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop帐户逾期+-*/.&^$#@!~<>[]{}\|`"0123456789。0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop0123456789asdfghjklzxcvbmnqwertyuiop');
END;
/
call account_status3_01();

--step5: 创建存储过程;expect: 创建成功，调用存储过程报指定错误
CREATE PROCEDURE account_status4_01 (
    due_date DATE,
    today DATE
) AUTHID DEFINER
IS
BEGIN
    IF due_date < today THEN
        RAISE_APPLICATION_ERROR(-20111, '帐户逾期。',true);
    ELSIF due_date = today THEN
        RAISE_APPLICATION_ERROR(-20998, '0123456789',false);
    ELSE
        RAISE_APPLICATION_ERROR(-20222, '+-*/.&^$#@!~<>[]{}\|`"');
    END IF;
END;
/
call account_status4_01('2024-1-1','2024-10-1');
call account_status4_01('2024-1-1','2024-1-1');
call account_status4_01('2024-1-1','2023-1-1');
call account_status4_01('2024-1-1','2024-10-1');
call account_status4_01('2024-1-1','2024-1-1');
call account_status4_01('2024-1-1','2023-1-1');

--step6: 创建存储过程;expect: 创建成功，调用存储过程报指定错误
CREATE or replace PROCEDURE account_status5_01 (
    due_date DATE,
    today DATE
) AUTHID DEFINER
IS
BEGIN
    IF due_date < today THEN
        RAISE_APPLICATION_ERROR(-20111.1, '帐户逾期。',true);
    ELSIF due_date = today THEN
        RAISE_APPLICATION_ERROR(-20997.56789, 56789,false);
    ELSE
        RAISE_APPLICATION_ERROR(-20222.123456789, '123456789');
    END IF;
END;
/
call account_status5_01('2024-1-1','2024-10-1');
call account_status5_01('2024-1-1','2024-1-1');
call account_status5_01('2024-1-1','2023-1-1');
CREATE or replace PROCEDURE pro6_01 (col1 int,col2 int)
IS
BEGIN
    IF col1 - col2 <=-10 THEN
        RAISE_APPLICATION_ERROR(-29999+9000, '+',true);
    ELSIF col1 - col2 < 0 THEN
        RAISE_APPLICATION_ERROR(-10000-10100, '-',false);
    ELSIF col1 - col2 = 0 THEN
        RAISE_APPLICATION_ERROR(-2611*8, '*');
    ELSE
        RAISE_APPLICATION_ERROR(-100000/5-15, '/');
    END IF;
END;
/
call pro6_01(-5,110);
call pro6_01(1,10);
call pro6_01(3,3);
call pro6_01(23,3);

--step7: 清理环境;expect: 成功
drop PROCEDURE if exists proc_exception_01;
drop table if exists employees_01;
drop PROCEDURE if exists account_status1_01;
drop PROCEDURE if exists account_status2_01;
drop PROCEDURE if exists account_status3_01;
drop PROCEDURE if exists account_status4_01;
drop PROCEDURE if exists account_status5_01;
drop PROCEDURE if exists pro6_01;
